-- @owner: @songjing20
-- @date: 2023-1-14
-- @testpoint: desc/describe分析update/upsert语句
-- @modified by @zou_jialiang050 2024/4/30:根据最新代码优化用例

--step1:建表插入数据;expect:成功
drop table if exists t_describe_0069 cascade;
create table t_describe_0069 (id bigint primary key, wholesale decimal(6,2) not null default 0.00, retail decimal(6,2) not null default 0.00, quantity bigint not null default 0);
insert into t_describe_0069 values(112, 26.5, 28.5, 86), (2, 1.7, 5.0, 5164), (3, 4.1, 5.5, 63);

--step2:开启事务，describe查看update执行计划，desc analyze实际执行计划，回滚;expect:执行成功，打印执行计划，回滚成功
begin;/
describe update t_describe_0069 set quantity = 98 where id = 112;
rollback;
select * from t_describe_0069;

--step3:upsert一条主键重复值(update)，describe分析执行计划;expect:打印执行计划
describe insert into t_describe_0069 values(2, 1.7, 5.0, 5164) on duplicate key update quantity = quantity + 5164;
desc insert into t_describe_0069 values(2, 1.7, 5.0, 5164) on duplicate key update quantity = quantity + 5164;

--step4:upsert不重复的值(insert),describe分析执行计划;expect:打印执行计划
describe insert into t_describe_0069 values(10, 11.7, 15.0, 564) on duplicate key update quantity = quantity + 564;
desc analyze insert into t_describe_0069 values(2, 1.7, 5.0, 5164) on duplicate key update quantity = quantity + 5164;

--step5:清理环境;expect:成功
drop table if exists t_describe_0069 cascade;